// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Web;
using System.Web.Configuration;
using net.windward.utils.ado.Unknown;
using NUnit.Framework;

namespace net.windward.utils.ado
{
	[TestFixture]
	public class TestUnknownProvider
	{

		private string mySqlServerName;
		private string mySqlDatabaseName;
		private string mySqlUsername;
		private string mySqlPassword;

		private string sqlServerServerName;
		private string sqlServerDatabaseName;
		private string sqlServerUsername;
		private string sqlServerPassword;

		readonly DbProviderFactory sqlServerProvider = DbProviderFactories.GetFactory("System.Data.SqlClient");
		readonly DbProviderFactory mySqlProvider = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");

		[SetUp]
		public void Init()
		{
			NameValueCollection appSettings;
			if (HttpContext.Current == null)
				appSettings = ConfigurationManager.AppSettings;
			else
				appSettings = WebConfigurationManager.AppSettings;

			mySqlServerName = appSettings["MySqlServer"];
			mySqlDatabaseName = appSettings["MySqlDatabase"];
			mySqlUsername = appSettings["MySqlUsername"];
			mySqlPassword = appSettings["MySqlPassword"];

			sqlServerServerName = appSettings["SqlServerServer"];
			sqlServerDatabaseName = appSettings["SqlServerDatabase"];
			sqlServerUsername = appSettings["SqlServerUsername"];
			sqlServerPassword = appSettings["SqlServerPassword"];
		}

		[Test]
		public void TestCreateProvider()
		{

			WrUnknownVendor vendor = new WrUnknownVendor("bogus", null, "System.Data.SqlClient"); // use SqlServer for fake class
			Assert.IsNotNull(vendor);
			Assert.AreEqual(false, vendor.CanEnumerateDatabases);
			Assert.AreEqual(false, vendor.CanLaunchAdministrator);
			Assert.AreEqual(false, vendor.CanRunScripts);
			Assert.AreEqual(WrVendor.DATABASE_MODE.OPTIONAL, vendor.DatabaseMode);
			Assert.AreEqual(false, vendor.HasProviders);
			Assert.AreEqual("bogus", vendor.Name);
			Assert.AreEqual(string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", sqlServerServerName, sqlServerDatabaseName, sqlServerUsername, sqlServerPassword),
				vendor.ConnectionString(sqlServerServerName, sqlServerDatabaseName, new WrCredentials(sqlServerUsername, sqlServerPassword), true));
			Assert.AreEqual(string.Format("Data Source={0};Initial Catalog={1}", sqlServerServerName, sqlServerDatabaseName),
				vendor.ConnectionString(sqlServerServerName, sqlServerDatabaseName, new WrCredentials(), true));

			Assert.IsNotNull(vendor.CreateCommand());
			Assert.IsNotNull(vendor.CreateConnection());
			Assert.IsNotNull(vendor.CreateParameter());
		}

		[Test]
		public void TestMySqlEnumeration()
		{

			WrUnknownServer server = new WrUnknownServer(mySqlProvider, mySqlServerName);
			IWrDatabase db = server.CreateDatabase(mySqlDatabaseName);
			WrCredentials cred = new WrCredentials(mySqlUsername, mySqlPassword);

			// gets everything
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.ALL, null);
			WrTable[] tables = db.Tables;
			Assert.IsTrue(tables.Length >= 16);
			WrView[] views = db.Views;
			Assert.IsTrue(views.Length >= 7);

			/* gets everything
			// Sakila tables
			WrTable[] tables = db.GetTables(cred, WrVendor.ELEM_OWNER.ALL);
			Assert.AreEqual(16, tables.Length);
			tables = db.GetTables(cred, WrVendor.ELEM_OWNER.SYSTEM);
			Assert.AreEqual(0, tables.Length);
			tables = db.GetTables(cred, WrVendor.ELEM_OWNER.USER);
			Assert.AreEqual(16, tables.Length);
			Assert.AreEqual("actor", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("store", tables[15].FullName);
			Assert.IsFalse(tables[15].IsSystem);

			tables = db.GetTables(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA);
			Assert.AreEqual(16, tables.Length);
			Assert.AreEqual("actor", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("store", tables[15].FullName);
			Assert.IsFalse(tables[15].IsSystem);

			// Sakila views
			WrView[] views = db.GetViews(cred, WrVendor.ELEM_OWNER.ALL);
			Assert.AreEqual(7, views.Length);
			views = db.GetViews(cred, WrVendor.ELEM_OWNER.SYSTEM);
			Assert.AreEqual(0, views.Length);
			views = db.GetViews(cred, WrVendor.ELEM_OWNER.USER);
			Assert.AreEqual(7, views.Length);
			Assert.AreEqual("actor_info", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);
			Assert.AreEqual("staff_list", views[6].FullName);
			Assert.IsFalse(views[6].IsSystem);

			views = db.GetViews(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA);
			Assert.AreEqual(7, views.Length);
			Assert.AreEqual("actor_info", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);
			Assert.AreEqual("staff_list", views[6].FullName);
			Assert.IsFalse(views[6].IsSystem);

			// Sakila stored procedures
			WrStoredProcedure[] procs = db.GetStoredProcedures(cred, WrVendor.ELEM_OWNER.ALL);
			Assert.AreEqual(6, procs.Length);
			procs = db.GetStoredProcedures(cred, WrVendor.ELEM_OWNER.SYSTEM);
			Assert.AreEqual(0, procs.Length);
			procs = db.GetStoredProcedures(cred, WrVendor.ELEM_OWNER.USER);
			Assert.AreEqual(6, procs.Length);
			Assert.AreEqual("film_in_stock", procs[0].FullName);
			Assert.IsFalse(procs[0].IsSystem);
			Assert.AreEqual("rewards_report", procs[5].FullName);
			Assert.IsFalse(procs[5].IsSystem);

			procs = db.GetStoredProcedures(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA);
			Assert.AreEqual(6, procs.Length);
			Assert.AreEqual("film_in_stock", procs[0].FullName);
			Assert.IsFalse(procs[0].IsSystem);
			Assert.AreEqual("rewards_report", procs[5].FullName);
			Assert.IsFalse(procs[5].IsSystem);
			 * */

			// Sakila.staff columns
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER, null);
			WrColumn[] cols = db.Tables[30].Columns;
			Assert.AreEqual(11, cols.Length);
			Assert.AreEqual("staff_id", cols[0].FullName);
			Assert.AreEqual(DbType.Byte, cols[0].DbType);
			Assert.AreEqual("password", cols[9].FullName);
			Assert.AreEqual(DbType.String, cols[9].DbType);

			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.DESCRIPTIONS, null);
			cols = db.Tables[30].Columns;
			Assert.AreEqual(11, cols.Length);
			Assert.AreEqual("staff_id", cols[0].FullName);
			Assert.AreEqual(DbType.Byte, cols[0].DbType);
			Assert.AreEqual("password", cols[9].FullName);
			Assert.AreEqual(DbType.String, cols[9].DbType);

			// test rental table pk-fk relationships were extracted
			db.LoadMetadata(cred, WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			cols = db.Tables[28].Columns;
//bugbug			Assert.AreEqual("staff.staff_id", cols[5].PrimaryKey);
		}

		[Test]
		public void TestNorthwindEnumeration()
		{
			// enum databases, make sure has test one
			WrUnknownServer server = new WrUnknownServer(sqlServerProvider, sqlServerServerName);
			Assert.AreEqual(sqlServerServerName, server.Name);
			IWrDatabase[] databases = server.GetDatabases(new WrCredentials(sqlServerUsername, sqlServerPassword));
			Assert.AreEqual(0, databases.Length);
			WrUnknownDatabase db = new WrUnknownDatabase(sqlServerProvider, sqlServerServerName, sqlServerDatabaseName);

			// Northwind tables
			db.LoadMetadata(new WrCredentials(sqlServerUsername, sqlServerPassword), WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.META_DATA, null);
			WrTable[] tables = db.Tables;
			Assert.AreEqual(13, tables.Length);
			Assert.AreEqual("Categories", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("Territories", tables[12].FullName);
			Assert.IsFalse(tables[12].IsSystem);

			// Northwind views
			WrView[] views = db.Views;
			Assert.AreEqual(16, views.Length);
			Assert.AreEqual("Alphabetical list of products", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);
			Assert.AreEqual("Summary of Sales by Year", views[15].FullName);
			Assert.IsFalse(views[15].IsSystem);

			// Northwind stored procedures
			WrStoredProcedure[] procs = db.StoredProcedures;
			Assert.AreEqual(7, procs.Length);
			Assert.AreEqual("CustOrderHist", procs[0].FullName);
			Assert.IsFalse(procs[0].IsSystem);
			Assert.AreEqual("Ten Most Expensive Products", procs[6].FullName);
			Assert.IsFalse(procs[6].IsSystem);

			// Northwind.Orders columns
			WrColumn[] cols = db.Tables[7].Columns;
			Assert.AreEqual(14, cols.Length);
			Assert.AreEqual("OrderID", cols[0].FullName);
			Assert.AreEqual(DbType.Int32, cols[0].DbType);
			Assert.AreEqual(null, cols[0].PrimaryKey);

			Assert.AreEqual("CustomerID", cols[1].FullName);
//bugbug			Assert.AreEqual(DbType.StringFixedLength, cols[1].DbType);
			Assert.AreEqual("Customers.CustomerID", cols[1].PrimaryKey);

			Assert.AreEqual("EmployeeID", cols[2].FullName);
			Assert.AreEqual(DbType.Int32, cols[2].DbType);
			Assert.AreEqual("Employees.EmployeeID", cols[2].PrimaryKey);

			Assert.AreEqual("ShipCountry", cols[13].FullName);
//bugbug			Assert.AreEqual(DbType.String, cols[13].DbType);
		}


	}
}
